#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
湖州地区数据查看器
用于查询和展示德清、长兴、安吉的街道社区和小区信息
"""

import sqlite3
import json
from typing import List, Dict
import pandas as pd

class HuzhouDataViewer:
    """湖州地区数据查看器"""
    
    def __init__(self, db_path: str = "huzhou_real_data.db"):
        self.db_path = db_path
    
    def get_connection(self):
        """获取数据库连接"""
        return sqlite3.connect(self.db_path)
    
    def get_districts_summary(self) -> Dict:
        """获取地区摘要信息"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 获取每个地区的统计信息
        cursor.execute("""
            SELECT 
                s.district_name,
                COUNT(DISTINCT s.adcode) as street_count,
                COUNT(DISTINCT c.adcode) as community_count,
                COUNT(DISTINCT r.id) as residential_area_count
            FROM streets s
            LEFT JOIN communities c ON s.adcode = c.parent_adcode
            LEFT JOIN residential_areas r ON c.name = r.community_name
            GROUP BY s.district_name
        """)
        
        results = cursor.fetchall()
        conn.close()
        
        summary = {}
        for row in results:
            district, streets, communities, areas = row
            summary[district] = {
                "街道数量": streets,
                "社区数量": communities,
                "小区数量": areas
            }
        
        return summary
    
    def get_streets_by_district(self, district_name: str) -> List[Dict]:
        """获取指定地区的街道信息"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT name, adcode, level
            FROM streets
            WHERE district_name = ?
            ORDER BY name
        """, (district_name,))
        
        streets = [{"name": row[0], "adcode": row[1], "level": row[2]} for row in cursor.fetchall()]
        conn.close()
        
        return streets
    
    def get_communities_by_street(self, street_name: str) -> List[Dict]:
        """获取指定街道的社区信息"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT name, adcode, level
            FROM communities
            WHERE street_name = ?
            ORDER BY name
        """, (street_name,))
        
        communities = [{"name": row[0], "adcode": row[1], "level": row[2]} for row in cursor.fetchall()]
        conn.close()
        
        return communities
    
    def get_residential_areas_by_community(self, community_name: str) -> List[Dict]:
        """获取指定社区的小区信息"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT name, address, location
            FROM residential_areas
            WHERE community_name = ?
            ORDER BY name
        """, (community_name,))
        
        areas = [{"name": row[0], "address": row[1], "location": row[2]} for row in cursor.fetchall()]
        conn.close()
        
        return areas
    
    def search_by_keyword(self, keyword: str) -> Dict:
        """根据关键词搜索"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 搜索街道
        cursor.execute("""
            SELECT name, district_name, 'street' as type
            FROM streets
            WHERE name LIKE ?
        """, (f"%{keyword}%",))
        streets = cursor.fetchall()
        
        # 搜索社区
        cursor.execute("""
            SELECT name, street_name, district_name, 'community' as type
            FROM communities
            WHERE name LIKE ?
        """, (f"%{keyword}%",))
        communities = cursor.fetchall()
        
        # 搜索小区
        cursor.execute("""
            SELECT name, community_name, street_name, district_name, 'residential_area' as type
            FROM residential_areas
            WHERE name LIKE ?
        """, (f"%{keyword}%",))
        areas = cursor.fetchall()
        
        conn.close()
        
        return {
            "streets": [{"name": row[0], "district": row[1], "type": row[2]} for row in streets],
            "communities": [{"name": row[0], "street": row[1], "district": row[2], "type": row[3]} for row in communities],
            "residential_areas": [{"name": row[0], "community": row[1], "street": row[2], "district": row[3], "type": row[4]} for row in areas]
        }
    
    def export_to_excel(self, output_file: str = "huzhou_area_data.xlsx"):
        """导出数据到Excel文件"""
        conn = self.get_connection()
        
        # 读取数据
        streets_df = pd.read_sql_query("SELECT * FROM streets", conn)
        communities_df = pd.read_sql_query("SELECT * FROM communities", conn)
        areas_df = pd.read_sql_query("SELECT * FROM residential_areas", conn)
        
        conn.close()
        
        # 创建Excel文件
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            streets_df.to_excel(writer, sheet_name='街道信息', index=False)
            communities_df.to_excel(writer, sheet_name='社区信息', index=False)
            areas_df.to_excel(writer, sheet_name='小区信息', index=False)
        
        print(f"数据已导出到 {output_file}")
    
    def print_hierarchical_view(self):
        """打印层级视图"""
        districts = ["德清县", "长兴县", "安吉县"]
        
        print("\n" + "="*80)
        print("湖州地区行政区划层级视图")
        print("="*80)
        
        for district in districts:
            print(f"\n📁 {district}")
            print("-" * 40)
            
            streets = self.get_streets_by_district(district)
            for street in streets:
                print(f"  📂 {street['name']}")
                
                communities = self.get_communities_by_street(street['name'])
                for community in communities:
                    print(f"    📄 {community['name']}")
                    
                    areas = self.get_residential_areas_by_community(community['name'])
                    for area in areas:
                        print(f"      🏠 {area['name']}")
    
    def interactive_search(self):
        """交互式搜索"""
        print("\n🔍 交互式搜索")
        print("输入关键词搜索街道、社区或小区信息")
        print("输入 'quit' 退出")
        
        while True:
            keyword = input("\n请输入搜索关键词: ").strip()
            
            if keyword.lower() == 'quit':
                break
            
            if not keyword:
                continue
            
            results = self.search_by_keyword(keyword)
            
            print(f"\n搜索结果 (关键词: {keyword}):")
            print("-" * 50)
            
            if results['streets']:
                print("\n🏢 街道:")
                for street in results['streets']:
                    print(f"  • {street['name']} ({street['district']})")
            
            if results['communities']:
                print("\n🏘️ 社区:")
                for community in results['communities']:
                    print(f"  • {community['name']} - {community['street']} ({community['district']})")
            
            if results['residential_areas']:
                print("\n🏠 小区:")
                for area in results['residential_areas']:
                    print(f"  • {area['name']} - {area['community']} - {area['street']} ({area['district']})")
            
            if not any(results.values()):
                print("未找到匹配的结果")


def main():
    """主函数"""
    viewer = HuzhouDataViewer()
    
    # 显示摘要信息
    summary = viewer.get_districts_summary()
    print("\n📊 湖州地区数据摘要")
    print("="*50)
    for district, stats in summary.items():
        print(f"\n{district}:")
        for key, value in stats.items():
            print(f"  {key}: {value}")
    
    # 显示层级视图
    viewer.print_hierarchical_view()
    
    # 交互式搜索
    viewer.interactive_search()
    
    # 导出到Excel
    viewer.export_to_excel()


if __name__ == "__main__":
    main() 